package oracle.to.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

/**
 * oracle to mysql db structure
 *
 */
public class OracleToMysql_Structure {

	static String ora_url = "jdbc:oracle:thin:@localhost:1521:orcl";// oracle连接串
	static String ora_user = "hr";// oracle用户名
	static String ora_pwd = "hr";// oracle密码

	private static String my_schema = "hr";// mysql实例名
	private static String TABLE = "'CB20_OLD','AC01'";// 多个用，分割

	private static Logger log = Logger.getLogger(" ");

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(ora_url, ora_user, ora_pwd);
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		List<String> tables = getTables(stmt, rs);
		for (int i = 0; i < tables.size(); i++) {
			String table = tables.get(i);
			// 获取表名
			Map<String, String> cms = getTableColumns(conn.createStatement(), rs, table);
			// 获取列名
			Map<String, Boolean> ncol = getTableColumnNames(conn.createStatement(), rs, table);
			// 获取表注释
			String tcm = getTableComments(conn.createStatement(), rs, table);
			// 获取约束
			List<String> pk = getTableConstraints(conn.createStatement(), rs, table);
			StringBuilder sb = new StringBuilder(256);
			// mysql建表
			createMysql(conn.createStatement(), rs, table, sb, pk, cms, ncol, tcm);
			log.info("\n"+sb.toString());
		}
		conn.close();
	}

	public static List<String> getTables(Statement stmt, ResultSet rs) throws SQLException {
		rs = stmt.executeQuery("select table_name  from user_tables where table_name in (" + TABLE + ") order by table_name ");
		List<String> tables = new ArrayList<String>();
		while (rs.next()) {
			tables.add(rs.getString(1).toUpperCase());
		}
		rs.close();
		stmt.close();
		return tables;
	}

	public static Map<String, String> getTableColumns(Statement stmt, ResultSet rs, String table) throws SQLException {
		// 获取表名
		rs = stmt.executeQuery("select column_name, comments from USER_COL_COMMENTS where table_name='" + table + "'");
		Map<String, String> cms = new HashMap<String, String>();
		while (rs.next()) {
			String col = rs.getString(1);
			String cm = rs.getString(2);
			if (cm != null && !"".equals(cm)) {
				cms.put(col.toUpperCase(), cm);
			}
		}
		rs.close();
		stmt.close();
		return cms;
	}

	public static Map<String, Boolean> getTableColumnNames(Statement stmt, ResultSet rs, String table)
			throws SQLException {
		// 获取列名
		rs = stmt.executeQuery(
				"select column_name from user_tab_cols where table_name = '" + table + "' and nullable = 'N'");
		Map<String, Boolean> ncol = new HashMap<String, Boolean>();
		while (rs.next()) {
			ncol.put(rs.getString(1).toUpperCase(), true);
		}
		rs.close();
		stmt.close();
		return ncol;
	}

	public static String getTableComments(Statement stmt, ResultSet rs, String table) throws SQLException {
		// 获取表注释
		rs = stmt.executeQuery("select comments from USER_TAB_COMMENTS where table_name='" + table + "'");
		String tcm = null;
		if (rs.next()) {
			String cm = rs.getString(1);
			if (cm != null && !"".equals(cm)) {
				tcm = cm;
			}
		}
		rs.close();
		stmt.close();
		return tcm;
	}

	public static List<String> getTableConstraints(Statement stmt, ResultSet rs, String table) throws SQLException {
		// 获取约束
		rs = stmt.executeQuery(
				"select cu.COLUMN_NAME from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = '"
						+ table + "'");
		List<String> pk = new ArrayList<String>();
		while (rs.next()) {
			pk.add(rs.getString(1).toUpperCase());
		}
		rs.close();
		stmt.close();
		return pk;
	}

	private static void createMysql(Statement stmt, ResultSet rs, String table, StringBuilder sb, List<String> pk,
			Map<String, String> cms, Map<String, Boolean> ncol, String tcm) throws SQLException {
		// mysql建表
		sb.append("CREATE TABLE `" + my_schema + "`.`" + table + "` (\r\n");
		rs = stmt.executeQuery("select * from " + table + " where rownum = 1");
		ResultSetMetaData mt = rs.getMetaData();
		int cols = mt.getColumnCount();
		for (int c = 1; c <= cols; c++) {
			int type = mt.getColumnType(c);// 列类别
			String name = mt.getColumnName(c).toUpperCase();// 列名
			int pre = mt.getPrecision(c);
			int sca = mt.getScale(c);
			sb.append(" ");
			sb.append("`").append(name).append("` ");
			if (type == 12) {
				if (pk.contains(name) && pre > 255) {
					pre = 255;
				}
				if (pk.contains(name)) {
					sb.append("VARCHAR(" + pre + ") CHARACTER SET 'utf8' COLLATE 'utf8_bin' ");
				} else {
					sb.append("VARCHAR(" + pre + ") ");
				}
			} else if (type == 2) {
				if (pk.contains(name)) {
					sb.append("DECIMAL(16,0) ");
				} else {
					if (sca == -127) {
						sb.append("DECIMAL(" + (pre == 0 ? 10 : pre) + ") ");
					} else {
						sb.append("DECIMAL(" + pre + "," + sca + ") ");
					}
				}
			} else if (type == 91 || type == 93) {
				sb.append("DATETIME ");
			} else if (type == 2004) {
				sb.append("MEDIUMBLOB ");
			} else if (type == 2005) {
				sb.append("MEDIUMTEXT ");
			} else if (type == 1) {
				sb.append("CHAR(" + pre + ") ");
			}
			if (pk.contains(name) || ncol.containsKey(name)) {
				sb.append("NOT NULL");
			} else {
				sb.append("NULL");
			}
			if (cms.containsKey(name)) {
				sb.append(" COMMENT '" + cms.get(name).trim().replaceAll("\r\n", "") + "'");
			}
			sb.append(",\r\n");
		}
		rs.close();
		stmt.close();
		if (pk.size() != 0) {
			String tmp = "";
			for (int p = 0; p < pk.size(); p++) {
				if (p != 0) {
					tmp += ", ";
				}
				tmp += ("`" + pk.get(p) + "`");
			}
			sb.append("PRIMARY KEY (" + tmp + "),\r\n");
		}
		if (sb.charAt(sb.length() - 3) == ',') {
			sb.deleteCharAt(sb.length() - 3);
		}
		sb.append(")");
		if (tcm != null) {
			sb.append(" COMMENT = '" + tcm.trim().replaceAll("\r\n", "") + "'");
		}
		sb.append(";\r\n\r\n\r\n");
	}

}